Exploring the fan trap
Here’s a few things to try when looking at one of these problems.
The cartesian product
Remove the sum() calls and remove the group by from the SQL. What does this tell us?
SELECT
c.customer_name,
ol.quantity * ol.unit_price AS order_line_total,
st.target_amount AS sales_target_line
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.customer_id
JOIN order_lines AS ol
ON ol.order_id = o.order_id
JOIN sales_targets AS st
ON st.customer_id = c.customer_id;
This gives us
+-----------------+------------------+-------------------+
| customer_name | order_line_total | sales_target_line |
|-----------------+------------------+-------------------|
| Acme Industries | 39.98 | 250.00 |
| Acme Industries | 49.50 | 250.00 |
| Acme Industries | 9.99 | 250.00 |
+-----------------+------------------+-------------------+
This means that the single value of the sales target is included 3 times, once for each order line. We have a cartesian join that will affect the sum of the values. If we had many targets, say for each month of the year, then the order line totals would be mutiplied by the number of targets. Note that for ease of understanding the July 2027 data wasn’t included because that would mean 2 month 7s and we’d have to complicate the queries beyond wha’ts needed for mere illustration of a point.
INSERT INTO sales_targets (customer_id, target_period, target_amount)
VALUES
(
(SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
DATE '2026-08-01',
250.00
),
(
(SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
DATE '2026-09-01',
250.00
),
(
(SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
DATE '2026-10-01',
250.00
),
(
(SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
DATE '2026-11-01',
250.00
),
(
(SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
DATE '2026-12-01',
250.00
),
(
(SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
DATE '2027-01-01',
250.00
),
(
(SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
DATE '2027-02-01',
250.00
),
(
(SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
DATE '2027-03-01',
250.00
),
(
(SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
DATE '2027-04-01',
250.00
),
(
(SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
DATE '2027-05-01',
250.00
),
(
(SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
DATE '2027-06-01',
250.00
);
Now, if we re-run the query that does the totals
SELECT
c.customer_name,
SUM(ol.quantity * ol.unit_price) AS order_total,
SUM(st.target_amount) AS sales_target_total
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.customer_id
JOIN order_lines AS ol
ON ol.order_id = o.order_id
JOIN sales_targets AS st
ON st.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name;
+-----------------+-------------+--------------------+
| customer_name | order_total | sales_target_total |
|-----------------+-------------+--------------------|
| Acme Industries | 1293.11 | 9750.00 |
+-----------------+-------------+--------------------+
The underlying data makes it even more obvious:
SELECT
c.customer_name,
ol.quantity * ol.unit_price AS order_line_total,
st.target_amount AS sales_target_line
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.customer_id
JOIN order_lines AS ol
ON ol.order_id = o.order_id
JOIN sales_targets AS st
ON st.customer_id = c.customer_id;
+-----------------+------------------+-------------------+
| customer_name | order_line_total | sales_target_line |
|-----------------+------------------+-------------------|
| Acme Industries | 39.98 | 250.00 |
| Acme Industries | 39.98 | 250.00 |
| Acme Industries | 39.98 | 250.00 |
| Acme Industries | 39.98 | 250.00 |
| Acme Industries | 39.98 | 250.00 |
| Acme Industries | 39.98 | 250.00 |
| Acme Industries | 39.98 | 250.00 |
| Acme Industries | 39.98 | 250.00 |
| Acme Industries | 39.98 | 250.00 |
| Acme Industries | 39.98 | 250.00 |
| Acme Industries | 39.98 | 250.00 |
| Acme Industries | 39.98 | 250.00 |
| Acme Industries | 39.98 | 250.00 |
| Acme Industries | 49.50 | 250.00 |
| Acme Industries | 49.50 | 250.00 |
| Acme Industries | 49.50 | 250.00 |
| Acme Industries | 49.50 | 250.00 |
| Acme Industries | 49.50 | 250.00 |
| Acme Industries | 49.50 | 250.00 |
| Acme Industries | 49.50 | 250.00 |
| Acme Industries | 49.50 | 250.00 |
| Acme Industries | 49.50 | 250.00 |
| Acme Industries | 49.50 | 250.00 |
| Acme Industries | 49.50 | 250.00 |
| Acme Industries | 49.50 | 250.00 |
| Acme Industries | 49.50 | 250.00 |
| Acme Industries | 9.99 | 250.00 |
| Acme Industries | 9.99 | 250.00 |
| Acme Industries | 9.99 | 250.00 |
| Acme Industries | 9.99 | 250.00 |
| Acme Industries | 9.99 | 250.00 |
| Acme Industries | 9.99 | 250.00 |
| Acme Industries | 9.99 | 250.00 |
| Acme Industries | 9.99 | 250.00 |
| Acme Industries | 9.99 | 250.00 |
| Acme Industries | 9.99 | 250.00 |
| Acme Industries | 9.99 | 250.00 |
| Acme Industries | 9.99 | 250.00 |
| Acme Industries | 9.99 | 250.00 |
+-----------------+------------------+-------------------+
How do we fix the fan trap?
We have 2 sets, one of which is the order lines, the other of which is the sales targets. So we need to split them into two different operations.
We can do this first for the sales targets. Let’s create ourselves a query that gets the sales targets by month:
SELECT
st.customer_id,
extract(month from st.target_period) as target_month,
SUM(st.target_amount) AS target_total
FROM sales_targets AS st
GROUP BY customer_id, target_month;
+-------------+--------------+--------------+
| customer_id | target_month | target_total |
|-------------+--------------+--------------|
| 1 | 12 | 250.00 |
| 1 | 5 | 250.00 |
| 1 | 1 | 250.00 |
| 1 | 4 | 250.00 |
| 1 | 11 | 250.00 |
| 1 | 3 | 250.00 |
| 1 | 9 | 250.00 |
| 1 | 7 | 500.00 |
| 1 | 2 | 250.00 |
| 1 | 10 | 250.00 |
| 1 | 6 | 250.00 |
| 1 | 8 | 250.00 |
+-------------+--------------+--------------+
Now let’s get the orders and their month by month too:
SELECT
o.customer_id,
o.order_id,
EXTRACT(month from o.order_date) as order_month,
SUM(ol.quantity * ol.unit_price) AS order_totals
FROM orders AS o
JOIN order_lines AS ol
ON ol.order_id = o.order_id
GROUP BY
o.customer_id,
o.order_id,
order_month;
+-------------+----------+-------------+------------------+
| customer_id | order_id | order_month | order_line_total |
|-------------+----------+-------------+------------------|
| 1 | 1 | 7 | 99.47 |
+-------------+----------+-------------+------------------+
We can turn these into inline tables and join them to the customer:
SELECT
c.customer_id,
c.customer_name,
o_totals.order_month,
o_totals.order_totals
FROM customers AS c
JOIN
(
SELECT
o.customer_id,
o.order_id,
EXTRACT(month from o.order_date) as order_month,
SUM(ol.quantity * ol.unit_price) AS order_totals
FROM orders AS o
JOIN order_lines AS ol
ON ol.order_id = o.order_id
GROUP BY
o.customer_id,
o.order_id,
order_month
) as o_totals
ON o_totals.customer_id = c.customer_id;
+-------------+-----------------+-------------+--------------+
| customer_id | customer_name | order_month | order_totals |
|-------------+-----------------+-------------+--------------|
| 1 | Acme Industries | 7 | 99.47 |
+-------------+-----------------+-------------+--------------+
We’ve removed the grouping and summing into the inline table. Now let’s add in the inline view for the sales targets as well:
SELECT
c.customer_id,
c.customer_name,
o_totals.order_month,
o_totals.order_totals,
target_totals.target_total
FROM customers AS c
JOIN
(
SELECT
o.customer_id,
EXTRACT(month from o.order_date) as order_month,
SUM(ol.quantity * ol.unit_price) AS order_totals
FROM orders AS o
JOIN order_lines AS ol
ON ol.order_id = o.order_id
GROUP BY
o.customer_id,
o.order_id,
order_month
) as o_totals
ON o_totals.customer_id = c.customer_id
JOIN (
SELECT
st.customer_id,
extract(month from st.target_period) as target_month,
SUM(st.target_amount) AS target_total
FROM sales_targets AS st
GROUP BY customer_id, target_month
) as target_totals
ON target_totals.customer_id = c.customer_id;
+-------------+-----------------+-------------+--------------+--------------+
| customer_id | customer_name | order_month | order_totals | target_total |
|-------------+-----------------+-------------+--------------+--------------|
| 1 | Acme Industries | 7 | 99.47 | 250.00 |
| 1 | Acme Industries | 7 | 99.47 | 250.00 |
| 1 | Acme Industries | 7 | 99.47 | 250.00 |
| 1 | Acme Industries | 7 | 99.47 | 250.00 |
| 1 | Acme Industries | 7 | 99.47 | 500.00 |
| 1 | Acme Industries | 7 | 99.47 | 250.00 |
| 1 | Acme Industries | 7 | 99.47 | 250.00 |
| 1 | Acme Industries | 7 | 99.47 | 250.00 |
| 1 | Acme Industries | 7 | 99.47 | 250.00 |
| 1 | Acme Industries | 7 | 99.47 | 250.00 |
| 1 | Acme Industries | 7 | 99.47 | 250.00 |
| 1 | Acme Industries | 7 | 99.47 | 250.00 |
+-------------+-----------------+-------------+--------------+--------------+
This gives the same answer 12 times, once for each target month, we also need to join our inline views on target month:
SELECT
c.customer_id,
c.customer_name,
o_totals.order_month,
o_totals.order_totals,
target_totals.target_total
FROM customers AS c
JOIN
(
SELECT
o.customer_id,
o.order_id,
EXTRACT(month from o.order_date) as order_month,
SUM(ol.quantity * ol.unit_price) AS order_totals
FROM orders AS o
JOIN order_lines AS ol
ON ol.order_id = o.order_id
GROUP BY
o.customer_id,
o.order_id,
order_month
) as o_totals
ON o_totals.customer_id = c.customer_id
JOIN (
SELECT
st.customer_id,
extract(month from st.target_period) as target_month,
SUM(st.target_amount) AS target_total
FROM sales_targets AS st
GROUP BY customer_id, target_month
) as target_totals
ON target_totals.customer_id = c.customer_id
WHERE target_totals.target_month = o_totals.order_month;
+-------------+-----------------+-------------+--------------+--------------+
| customer_id | customer_name | order_month | order_totals | target_total |
|-------------+-----------------+-------------+--------------+--------------|
| 1 | Acme Industries | 7 | 99.47 | 500.00 |
+-------------+-----------------+-------------+--------------+--------------+
We’ve now moved from a fan trap to a chasm trap. We have the correct values but now can’t see the targets for the rest of the year. Breaking this down we have 3 sets:
- Customer
- Sales targets
- Order totals
We can fix this with an outer join. It will pull everything from the joined table into the results. They come in two flavours, left and right. Left includes all of the data in the table driving the query, in our case customers, however we want all of the target data, so we want the right join. Drawing a Venn diagram often helps you resolve this:
SELECT
c.customer_id,
c.customer_name,
o_totals.order_month,
o_totals.order_totals,
target_totals.target_total
FROM customers AS c
JOIN
(
SELECT
o.customer_id,
EXTRACT(month from o.order_date) as order_month,
SUM(ol.quantity * ol.unit_price) AS order_totals
FROM orders AS o
JOIN order_lines AS ol
ON ol.order_id = o.order_id
GROUP BY
o.customer_id,
o.order_id,
order_month
) as o_totals
ON o_totals.customer_id = c.customer_id
LEFT JOIN (
SELECT
st.customer_id,
extract(month from st.target_period) as target_month,
SUM(st.target_amount) AS target_total
FROM sales_targets AS st
GROUP BY customer_id, target_month
) as target_totals
ON target_totals.customer_id = c.customer_id
AND target_totals.target_month = o_totals.order_month;
+-------------+-----------------+-------------+--------------+--------------+
| customer_id | customer_name | order_month | order_totals | target_total |
|-------------+-----------------+-------------+--------------+--------------|
| 1 | Acme Industries | 7 | 99.47 | 250.00 |
| <null> | <null> | <null> | <null> | 250.00 |
| <null> | <null> | <null> | <null> | 250.00 |
| <null> | <null> | <null> | <null> | 250.00 |
| <null> | <null> | <null> | <null> | 250.00 |
| <null> | <null> | <null> | <null> | 250.00 |
| <null> | <null> | <null> | <null> | 250.00 |
| <null> | <null> | <null> | <null> | 250.00 |
| <null> | <null> | <null> | <null> | 250.00 |
| <null> | <null> | <null> | <null> | 250.00 |
| <null> | <null> | <null> | <null> | 250.00 |
| <null> | <null> | <null> | <null> | 250.00 |
+-------------+-----------------+-------------+--------------+--------------+
Notice also that we had to change the where at the end of the query to an and so that it was included in the outer join. Leaving it as a where would have made the outer join not work. It needed to outer join to both of the other sets.
NOTE - this query isn’t quite right cos we should be seeing cust number and name in the outer join. Might need to create another subquery? See if you can make it work for you.
SELECT
c_target_totals.customer_id,
c_target_totals.customer_name,
o_totals.order_totals,
c_target_totals.target_month,
c_target_totals.target_total
FROM (
SELECT
c.customer_id,
c.customer_name,
target_totals.target_month,
target_totals.target_total
FROM customers AS c
LEFT JOIN (
SELECT
st.customer_id,
extract(month from st.target_period) as target_month,
SUM(st.target_amount) AS target_total
FROM sales_targets AS st
GROUP BY customer_id, target_month
) as target_totals
ON target_totals.customer_id = c.customer_id
) as c_target_totals
LEFT JOIN
(
SELECT
o.customer_id,
EXTRACT(month from o.order_date) as order_month,
SUM(ol.quantity * ol.unit_price) AS order_totals
FROM orders AS o
JOIN order_lines AS ol
ON ol.order_id = o.order_id
GROUP BY
o.customer_id,
order_month
) as o_totals
ON o_totals.customer_id = c_target_totals.customer_id
and o_totals.order_month = c_target_totals.target_month
order by target_month, customer_id
;
+-------------+------------------+--------------+--------------+--------------+
| customer_id | customer_name | order_totals | target_month | target_total |
|-------------+------------------+--------------+--------------+--------------|
| 1 | Acme Industries | <null> | 1 | 250.00 |
| 1 | Acme Industries | <null> | 2 | 250.00 |
| 1 | Acme Industries | <null> | 3 | 250.00 |
| 1 | Acme Industries | <null> | 4 | 250.00 |
| 1 | Acme Industries | <null> | 5 | 250.00 |
| 1 | Acme Industries | <null> | 6 | 250.00 |
| 1 | Acme Industries | 99.47 | 7 | 250.00 |
| 1 | Acme Industries | <null> | 8 | 250.00 |
| 1 | Acme Industries | <null> | 9 | 250.00 |
| 1 | Acme Industries | <null> | 10 | 250.00 |
| 1 | Acme Industries | <null> | 11 | 250.00 |
| 1 | Acme Industries | <null> | 12 | 250.00 |
| 2 | Acorn Industries | <null> | <null> | <null> |
+-------------+------------------+--------------+--------------+--------------+